package com.yanzhang.dao.user;

import com.mysql.cj.util.StringUtils;
import com.yanzhang.pojo.User;
import com.yanzhang.util.DBUtil;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yanzhang
 */
public class UserDaoImpl implements UserDao{

    /**
     * 新增一个用户
     * @param connection 数据库连接
     * @param user 用户对象
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int add(Connection connection, User user) throws Exception {
        int result = 0;
        if (connection != null && user != null) {
            PreparedStatement preparedStatement = null;
            String sql = "insert into smbms_user (userCode,userName,userPassword,userRole,gender,birthday,phone,address,creationDate,createdBy) values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {user.getUserCode(),user.getUserName(),user.getUserPassword(),user.getUserRole(),user.getGender(),user.getBirthday(), user.getPhone(),user.getAddress(),user.getCreationDate(),user.getCreatedBy()};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 通过用户名查询用户
     * @param connection 数据库连接
     * @param userCode 用户名
     * @return 用户对象
     * @throws SQLException 抛出SQL异常
     */
    @Override
    public User getUserByCode(Connection connection, String userCode) throws Exception {
        User user = null;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            String sql = "select * from smbms_user where userCode = ?";
            Object[] params = {userCode};
            rs = DBUtil.query(connection, preparedStatement, rs, sql, params);
            if (rs.next()) {
                user = getUserByResult(rs);
            }
            DBUtil.closeResource(preparedStatement, rs);
        }
        return user;
    }

    /**
     * 分页查询用户集合
     * @param connection 数据库连接
     * @param userName 用户名字
     * @param userRole 角色id
     * @param currentPageNo 当前页码
     * @param pageSize 每页数量
     * @return 用户对象集合
     * @throws Exception 抛出SQL异常
     */
    @Override
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception {
        List<User> users = new ArrayList<>();
        if (connection == null) {
            return users;
        }
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        StringBuilder sql = new StringBuilder();
        sql.append("select u.*, r.roleName as userRoleName from smbms_user u, smbms_role r where u.userRole = r.id");
        List<Object> list = new ArrayList<>();
        if (!StringUtils.isNullOrEmpty(userName)) {
            sql.append(" and u.userName like ?");
            list.add("%" + userName + "%");
        }
        if (userRole > 0) {
            sql.append(" and u.userRole = ?");
            list.add(userRole);
        }
        sql.append(" order by creationDate DESC limit ?, ?");
        currentPageNo = (currentPageNo - 1) * pageSize;
        list.add(currentPageNo);
        list.add(pageSize);
        Object[] params = list.toArray();
        rs = DBUtil.query(connection, preparedStatement, rs, sql.toString(), params);
        while (rs.next()) {
            User user = getUserByResult(rs);
            user.setUserRoleName(rs.getString("userRoleName"));
            users.add(user);
        }
        DBUtil.closeResource(preparedStatement, rs);
        return users;
    }

    /**
     * 查询用户总数量
     * @param connection 数据库连接
     * @param userName 用户名字
     * @param userRole 角色id
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int getUserCount(Connection connection, String userName, int userRole) throws Exception {
        int count = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            StringBuilder sql = new StringBuilder();
            sql.append("select count(1) as count from smbms_user u, smbms_role r where u.userRole = r.id");
            List<Object> list = new ArrayList<>();
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }
            if (userRole > 0) {
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }
            Object[] params = list.toArray();
            rs = DBUtil.query(connection, preparedStatement, rs, sql.toString(), params);
            if (rs.next()) {
                count = rs.getInt("count");
            }
            DBUtil.closeResource(preparedStatement, rs);
        }
        return count;
    }

    /**
     * 通过id获取用户
     * @param connection 数据库连接
     * @param id 主键
     * @return 用户对象
     * @throws Exception 抛出SQL异常
     */
    @Override
    public User getUserById(Connection connection, int id) throws Exception {
        User user = null;
        if (connection != null && id > 0) {
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            String sql = "select u.*, r.roleName as userRoleName from smbms_user u, smbms_role r where u.id = ? and u.userRole = r.id";
            Object[] params = {id};
            rs = DBUtil.query(connection, preparedStatement, rs, sql, params);
            if (rs.next()) {
                user = getUserByResult(rs);
                user.setUserRoleName(rs.getString("userRoleName"));
            }
            DBUtil.closeResource(preparedStatement, rs);
        }
        return user;
    }

    /**
     * 修改用户
     * @param connection 数据库连接
     * @param user 用户对象
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int modifyUser(Connection connection, User user) throws Exception {
        int result = 0;
        if (connection != null && user != null) {
            PreparedStatement preparedStatement = null;
            String sql = "update smbms_user set userName = ?,gender = ?,birthday = ?,phone = ?,address = ?,userRole = ?,modifyBy = ?,modifyDate = ? where id = ?";
            Object[] params = {user.getUserName(), user.getGender(), user.getBirthday(), user.getPhone(), user.getAddress(), user.getUserRole(), user.getModifyBy(), user.getModifyDate(), user.getId()};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 修改用户密码
     * @param connection 数据库连接
     * @param id 主键
     * @param password 用户新密码
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int updatePassword(Connection connection, int id, String password) throws Exception {
        int result = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            String sql = "update smbms_user set userPassword = ? where id = ?";
            Object[] params = {password, id};
            result = DBUtil.execute(connection, preparedStatement ,sql, params);
        }
        return result;
    }

    /**
     * 通过id删除一个用户
     * @param connection 数据库连接
     * @param id 主键
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int deleteUserById(Connection connection, int id) throws Exception {
        int result = 0;
        if (connection != null && id > 0) {
            PreparedStatement preparedStatement = null;
            String sql = "delete from smbms_user where id = ?";
            Object[] params = {id};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 通过查询结果生成user对象
     * @param rs sql查询结果
     * @return user对象
     * @throws SQLException 抛出SQL异常
     */
    private User getUserByResult (ResultSet rs) throws Exception {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setUserCode(rs.getString("userCode"));
        user.setUserName(rs.getString("userName"));
        user.setUserPassword(rs.getString("userPassword"));
        user.setGender(rs.getInt("gender"));
        user.setBirthday(rs.getDate("birthday"));
        user.setPhone(rs.getString("phone"));
        user.setAddress(rs.getString("address"));
        user.setUserRole(rs.getInt("userRole"));
        user.setCreatedBy(rs.getInt("createdBy"));
        user.setCreationDate(rs.getTimestamp("creationDate"));
        user.setModifyBy(rs.getInt("modifyBy"));
        user.setModifyDate(rs.getTimestamp("modifyDate"));
        return user;
    }

    @Test
    public void testLogin () {
        Connection connection = DBUtil.getConnection();
        try {
            User admin = getUserByCode(connection, "admin");
            System.out.println(admin.getUserPassword());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection);
        }
    }

    @Test
    public void testGetCount () {
        Connection connection = DBUtil.getConnection();
        try {
            int userCount = getUserCount(connection, "", 2);
            System.out.println(userCount);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection);
        }
    }

    @Test
    public void testGetList () {
        Connection connection = DBUtil.getConnection();
        try {
            List<User> userList = getUserList(connection, null, 0, 1, 5);
            System.out.println(userList.size());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection);
        }
    }

    @Test
    public void testGetById () {
        Connection connection = DBUtil.getConnection();
        try {
            User userById = getUserById(connection, 1);
            System.out.println(userById.getUserName());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection);
        }
    }
}
